﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Dapper;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using WebDulich.Repository.Dapper;

namespace WebDulich.Repository
{
    public class CategoryRepository:ICategoryRepository
    {
        private IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
        public Dapper.Category Insert(Dapper.Category record)
        {
            var sqlQuery = "INSERT INTO category(Name, Description, Type) VALUES(@Name,@Description,@Type); SELECT CAST(SCOPE_IDENTITY() as int)";
            record.Id = this._db.Query<int>(sqlQuery, record).Single();
            return record;
        }

        public bool Update(Dapper.Category record)
        {
            var sqlQuery = "UPDATE category SET Name=@Name, Description=@Description, Type=@Type WHERE Id=@Id";
            this._db.Query<Category>(sqlQuery, record);
            return true;
        }

        public bool Remove(string id)
        {
            var sqlQuery = "DELETE FROM category WHERE Id=" + id;
            this._db.Query<Category>(sqlQuery);
            return true;
        }

        public List<Dapper.Category> GetAll(int pIndex, int pSize, bool? active)
        {
            var sqlQuery = "SELECT * FROM category";
            return this._db.Query<Category>(sqlQuery).ToList();
        }

        public Dapper.Category Get(string id)
        {
            var sqlQuery = "SELECT * FROM category WHERE Id=" + id;
            return this._db.Query<Category>(sqlQuery).Single();
        }
    }
}